# This code is hosted on http://code.google.com/p/lenthorp/
# Freely available for use in applications, but should NOT be modified
# Email all comments to lenthorpresearch@gmail.com


import MySQLdb
from datetime import datetime

splitTableName = '__historicalSplits'

def LoadSQLDateRate(dbName, tableName, dateLower = '', dateHigher = '', adjustForSplits = False, username='root'):
    db = MySQLdb.connect("localhost", username, '', dbName)
    c = db.cursor()
    dates = []
    rates = []
    clause = []
    if not dateLower == '':
        clause.append("`Date` >= '" + dateLower.strftime('%Y-%m-%d') + "'")
    if not dateHigher == '':
        clause.append("`Date` <= '" + dateHigher.strftime('%Y-%m-%d') + "'")
    statement = " WHERE " + clause[0] if len(clause) > 0 else ''
    if len(clause) > 1:
        for idx in range(1,len(clause)):
            statement += " AND " + clause[1]
    c.execute("SELECT `Date`, `Last` FROM `" + tableName + '`' + statement + ";")
    data = c.fetchall()
    x = []
    y = []
    for d in data:
        x.append(d[0])
        y.append(d[1])
    data = [x,y]

    if adjustForSplits:
        c.execute("SELECT `Date`, `SplitRatio` FROM `" + splitTableName + "` WHERE Ticker = '" + tableName + "' ORDER By Date Desc;")
        splits = c.fetchall()
        if len(splits) > 0:
            splitIndex = 0
            for idx in range(len(data[0])-1,-1,-1):
                if data[0][idx] < splits[splitIndex][0]:
                    for jdx in range(idx,-1,-1):
                        data[1][jdx] /= splits[splitIndex][1]
                    splitIndex += 1
                    if splitIndex == len(splits):
                        break
    return data


def LoadSQLDateRateGivenDates(dbName, tableName, dates, username='root'):
    dates.sort()
    minDate = dates[0]
    maxDate = dates[-1]
    data = LoadSQLDateRate(dbName, tableName, minDate, maxDate, username)
    x = []
    y = []
    for d in dates:
        idx = data[0].index(d)
        x.append(data[0][idx])
        y.append(data[1][idx])
    data = [x,y]
    return data